"""
Case Type   : JDBC+mysql和opengauss兼容B库对比
Case Name   : JDBC默认参数+TINYINT(n)无效取值范围测试
Create At   : 2023/10/16
Owner       : ningyali
Description :
    1.读取通用tinyint SQL脚本
    2.复制jdbc到当前目录
    3.生成java文件,通过JDBC连接数据库,执行sql
    4.编译上一步生成的java文件，生成可执行文件
    5.执行上一步生成的可执行文件，对mysql和opengauss兼容B库结果进行对比
Expect      :
    1.执行成功
    2.执行成功
    3.执行成功
    4.执行成功
    5.执行成功，结果一致
History     :
"""
import os
import unittest

from yat.test import Node

from testcase.CONNECTORS.JDBC.CompatibilityB.DATATYPE. \
    Opengauss_Function_Connect_JDBC_Common import ComJDBC
from testcase.utils.Logger import Logger


class MysqlTest2(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        text = f'-----{os.path.basename(__file__)} start-----'
        self.log.info(text)
        self.comjdbc = ComJDBC()
        self.mysql_node = Node('mysql5')
        self.og_node = Node('PrimaryDbBmode')
        self.mysql_info = {'f_name': 'mysql_jdbc_datatype2',
                           'db_type': 'mysql',
                           'db_host': self.mysql_node.db_host,
                           'db_port': self.mysql_node.db_port,
                           'db_name': self.mysql_node.db_name,
                           'db_user': self.mysql_node.db_user,
                           'db_passwd': self.mysql_node.db_password,
                           'db_driver': 'com.mysql.jdbc.Driver',
                           'sql': ''}
        self.og_info = {'f_name': 'opengauss_jdbc_datatype2',
                        'db_type': 'opengauss',
                        'db_host': self.og_node.db_host,
                        'db_port': self.og_node.db_port,
                        'db_name': self.og_node.db_name,
                        'db_user': self.og_node.db_user,
                        'db_passwd': self.og_node.db_password,
                        'db_driver': 'org.opengauss.Driver',
                        'sql': ''}
        self.sql_path = os.path.join(
            'testcase', 'script', 'CompatibilityB', 'DATATYPE', 'TINYINT',
            'CompatibilityB_DataType_Tinyint_Case0002.sql')
        self.expect = "*** mysql\n" \
                      "--- openGauss\n" \
                      "***************\n" \
                      "*** 15,41 ****\n" \
                      "--step3:插入数据;expect:合理报错\n" \
                      "--超出左右边界\n" \
                      "insert t_tinyint0002 values(-129, -129, -129, -129)\n" \
                      "! ERROR:Data truncation: " \
                      "Out of range value for column 'c1' at row 1\n" \
                      "insert t_tinyint0002 values(128, 128, 128, 128)\n" \
                      "! ERROR:Data truncation: " \
                      "Out of range value for column 'c1' at row 1\n" \
                      "--插入特殊字符\n" \
                      "insert t_tinyint0002 values(" \
                      "'@#$%^&*!', '@#$%^&*!', '@#$%^&*!', '@#$%^&*!')\n" \
                      "! ERROR:Incorrect integer value: '@#$%^&*!' " \
                      "for column 'c1' at row 1\n" \
                      "--插入中文字符\n" \
                      "insert t_tinyint0002 values(" \
                      "'熊猫是国宝', '熊猫是国宝', '熊猫是国宝', '熊猫是国宝')\n" \
                      "! ERROR:Incorrect integer value: '熊猫是国宝' " \
                      "for column 'c1' at row 1\n" \
                      "--插入含数字字符串\n" \
                      "insert t_tinyint0002 values(" \
                      "'111', '111', '111', '111')\n" \
                      "SQL SUCCESS\n" \
                      "--插入含引号数字字符串\n" \
                      "insert t_tinyint0002 values(" \
                      "'\"111\"', '\"111\"', '\"111\"', '\"111\"')\n" \
                      "! ERROR:Incorrect integer value: '\"111\"' " \
                      "for column 'c1' at row 1\n" \
                      "--插入括号类特殊字符\n" \
                      "insert t_tinyint0002 values(" \
                      "'()[]{}', '()[]{}', '()[]{}', '()[]{}')\n" \
                      "! ERROR:Incorrect integer value: '()[]{}' " \
                      "for column 'c1' at row 1\n" \
                      "--插入小语种字符串\n" \
                      "insert into t_tinyint0002(c1) values(" \
                      "'αβγδεζηθικλμνξοπρστυφχψω')\n" \
                      "! ERROR:Incorrect integer value: " \
                      "'αβγδεζηθικλμνξοπρστυφχψω' for column 'c1' at row 1\n" \
                      "--step4:查询;expect:成功\n" \
                      "select * from t_tinyint0002\n" \
                      "----查询结果----\n" \
                      "--- 15,53 ----\n" \
                      "--step3:插入数据;expect:合理报错\n" \
                      "--超出左右边界\n" \
                      "insert t_tinyint0002 values(-129, -129, -129, -129)\n" \
                      "! ERROR:[IP003:59074/IP002:51258] " \
                      "ERROR: tinyint out of range\n" \
                      "!   Where: referenced column: c1\n" \
                      "insert t_tinyint0002 values(128, 128, 128, 128)\n" \
                      "! ERROR:[IP003:59074/IP002:51258] " \
                      "ERROR: tinyint out of range\n" \
                      "!   Where: referenced column: c1\n" \
                      "--插入特殊字符\n" \
                      "insert t_tinyint0002 values(" \
                      "'@#$%^&*!', '@#$%^&*!', '@#$%^&*!', '@#$%^&*!')\n" \
                      "! ERROR:[IP003:59074/IP002:51258] " \
                      "ERROR: invalid input syntax " \
                      "for type tinyint: \"@#$%^&*!\"\n" \
                      "!   Position: 29\n" \
                      "!   Where: referenced column: c1\n" \
                      "--插入中文字符\n" \
                      "insert t_tinyint0002 values(" \
                      "'熊猫是国宝', '熊猫是国宝', '熊猫是国宝', '熊猫是国宝')\n" \
                      "! ERROR:[IP003:59074/IP002:51258] " \
                      "ERROR: invalid input syntax " \
                      "for type tinyint: \"熊猫是国宝\"\n" \
                      "!   Position: 29\n" \
                      "!   Where: referenced column: c1\n" \
                      "--插入含数字字符串\n" \
                      "insert t_tinyint0002 values(" \
                      "'111', '111', '111', '111')\n" \
                      "SQL SUCCESS\n" \
                      "--插入含引号数字字符串\n" \
                      "insert t_tinyint0002 values(" \
                      "'\"111\"', '\"111\"', '\"111\"', '\"111\"')\n" \
                      "! ERROR:[IP003:59074/IP002:51258] " \
                      "ERROR: invalid input syntax " \
                      "for type tinyint: \"\"111\"\"\n" \
                      "!   Position: 29\n" \
                      "!   Where: referenced column: c1\n" \
                      "--插入括号类特殊字符\n" \
                      "insert t_tinyint0002 values(" \
                      "'()[]{}', '()[]{}', '()[]{}', '()[]{}')\n" \
                      "! ERROR:[IP003:59074/IP002:51258] " \
                      "ERROR: invalid input syntax " \
                      "for type tinyint: \"()[]{}\"\n" \
                      "!   Position: 29\n" \
                      "!   Where: referenced column: c1\n" \
                      "--插入小语种字符串\n" \
                      "insert into t_tinyint0002(c1) values(" \
                      "'αβγδεζηθικλμνξοπρστυφχψω')\n" \
                      "! ERROR:[IP003:59074/IP002:51258] " \
                      "ERROR: invalid input syntax " \
                      "for type tinyint: \"αβγδεζηθικλμνξοπρστυφχψω\"\n" \
                      "!   Position: 38\n" \
                      "!   Where: referenced column: c1\n" \
                      "--step4:查询;expect:成功\n" \
                      "select * from t_tinyint0002\n" \
                      "----查询结果----"

    def test_1(self):
        text = 'step1:读取tinyint SQL脚本; expect:执行成功'
        self.log.info(f'-----{text}-----')
        self.log.info(self.sql_path)
        sql = self.comjdbc.read_sql_file(self.sql_path)
        self.assertNotEqual(len(sql.strip()), 0, f'执行失败：{text}')
        self.mysql_info['sql'] = sql
        self.og_info['sql'] = sql

        text = 'step2:复制jdbc到当前目录; expect:执行成功'
        self.log.info(f'-----{text}-----')
        res = self.comjdbc.get_jdbc(self.mysql_info['db_type'])
        self.assertEqual(len(res.strip()), 0, f'mysql执行失败：{text}')
        res = self.comjdbc.get_jdbc(self.og_info['db_type'])
        self.assertEqual(len(res.strip()), 0, f'opengauss执行失败：{text}')

        text = 'step3:生成java文件,通过JDBC连接数据库,执行sql; expect:执行成功'
        self.log.info(f'-----{text}-----')
        self.comjdbc.write_java_file(**self.mysql_info)
        self.comjdbc.write_java_file(**self.og_info)

        text = 'step4:编译上一步生成的java文件，生成可执行文件; expect:执行成功'
        self.log.info(f'-----{text}-----')
        res = self.comjdbc.compile_java_file(self.mysql_info['db_type'])
        self.assertEqual(len(res.strip()), 0, f'mysql执行失败：{text}')
        res = self.comjdbc.compile_java_file(self.og_info['db_type'])
        self.assertEqual(len(res.strip()), 0, f'opengauss执行失败：{text}')

        text = 'step5:执行上一步生成的可执行文件，对mysql和opengauss兼容B库结果进行对比; ' \
               'expect:执行成功，结果一致'
        self.log.info(f'-----{text}-----')
        mysql_res, _, _ = self.comjdbc.run_java_file(
            self.mysql_info['db_type'])
        og_res, _, _ = self.comjdbc.run_java_file(self.og_info['db_type'])

        self.log.info('-----对比mysql和opengauss兼容B库执行结果-----')
        diff = self.comjdbc.compare_sql_res(mysql_res, og_res, 'mysql',
                                            'openGauss')
        self.log.info('-----对比预期结果与实际运行结果-----')
        diff_diff = self.comjdbc.compare_sql_err_res(self.expect, diff,
                                                     '预期结果', '实际结果')
        self.assertEqual(len(diff_diff.strip()), 0, '预期结果与实际运行结果不一致')

    def tearDown(self):
        self.log.info('----run teardown----')
        self.log.info('-----清理环境-----')
        self.comjdbc.remove_java_file()
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
